package edu.gatech.doi.spamframework.mapping;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;

public class TwitterMsgId2Userid {
	private HashMap messagespamhm=null;
	private String instancesdirectory=System.getProperties().getProperty("user.dir")+System.getProperties().getProperty("file.separator")+"instances"+System.getProperties().getProperty("file.separator");
	private HashMap senderidhm=null;
	public void init()
	{
		this.messagespamhm=new HashMap();
		String messagespamfilename=System.getProperties().getProperty("user.dir")+System.getProperties().getProperty("file.separator")+"suspended_status_ids";
		File messagespamfile=new File(messagespamfilename);
		this.senderidhm=new HashMap();
		String senderidfilename=System.getProperties().getProperty("user.dir")+System.getProperties().getProperty("file.separator")+"twittersenderidL1.txt";
		File senderidfile=new File(senderidfilename);
		
		try {
		
			FileReader frs2=new FileReader(messagespamfile);
			BufferedReader br2=new BufferedReader(frs2);
			String line=null;
			while((line=br2.readLine())!=null)
			{
				this.messagespamhm.put(line.trim(), "1");
			}
			br2.close();
			frs2.close();
			
			FileReader frs3=new FileReader(senderidfilename);
			BufferedReader br3=new BufferedReader(frs3);
			String line3=null;
			while((line3=br3.readLine())!=null)
			{
				this.senderidhm.put(line3.trim(), "1");
			}
			br3.close();
			frs3.close();
			
			
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void getUserid()
	{
		/**
		 * connect to db :Mysql
		 */
		
		Connection conn = null;

		try {
		System.out.println("before connecting...");
		conn =DriverManager.getConnection("jdbc:postgresql://matcha.cc.gatech.edu:5432/twitware?" +
		"user=wang6&password=?WDcj984615?");
		System.out.println("connect successfully");
		// Do something with the Connection
		Statement stmt = null;
		ResultSet rs = null;
		
		stmt = (Statement) conn.createStatement();
		//rs = stmt.executeQuery("SELECT foo FROM bar");
		// or alternatively, if you don't know ahead of time that
		// the query will be a SELECT...
		
		 stmt.execute("set search_path to twitware,public;");
		 System.out.println("set search_path to twitware,public;");
		 stmt.setFetchSize(100); 
		 
		 
		 //create file
		    String useridfilename="twitterspamsenderid.txt";
		    String uidfilename=this.instancesdirectory+useridfilename;   
		    File uidfile=new File(uidfilename);
		    if(!uidfile.exists())
		    {
		    	try {
		    		uidfile.createNewFile();
//				 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
	 
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		    }
		    else
		    {
		    	try {
			    	
						uidfile.delete();
						uidfile.createNewFile();
//					 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
		    		
		    	
	    		} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		    }
		    
		    FileWriter writer = null;
		    BufferedWriter bw=null;
	    	 
		    writer =new FileWriter(uidfilename, true);
	    	
		    bw=new BufferedWriter(writer);
		 
		 
		 
		 
		 Iterator it2=this.messagespamhm.keySet().iterator();
		 while(it2.hasNext())
		 {
			 String sid=(String)it2.next();	
			 stmt.execute("SELECT user_id FROM status where id='"+sid+"' ;");
				
			 System.out.println("SELECT user_info.name,status.user_id FROM user_info,status where status.id='"+sid+"' and user_info.id=status.user_id;");
			 System.out.println("start query "); 
			rs = stmt.getResultSet();
			System.out.println("done query "); 
			if(rs!=null)
			{
				rs.next();
				String userid=rs.getString(1);
				if(userid!=null)
				{
				 bw.newLine();
				 bw.write(userid);
	    		 
	    		 bw.flush();
				}
			}
		
				
		 }
		 bw.close();
    	 writer.close();
    	 bw=null;
    	 writer=null;
		 
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void getUserName()
	{
		/**
		 * connect to db :Mysql
		 */
		
		Connection conn = null;

		try {
		System.out.println("before connecting...");
		conn =DriverManager.getConnection("jdbc:postgresql://matcha.cc.gatech.edu:5432/twitware?" +
		"user=wang6&password=?WDcj984615?");
		System.out.println("connect successfully");
		// Do something with the Connection
		Statement stmt = null;
		ResultSet rs = null;
		
		stmt = (Statement) conn.createStatement();
		//rs = stmt.executeQuery("SELECT foo FROM bar");
		// or alternatively, if you don't know ahead of time that
		// the query will be a SELECT...
		
		 stmt.execute("set search_path to twitware,public;");
		 System.out.println("set search_path to twitware,public;");
		 stmt.setFetchSize(100); 
		 
		 
		 //create file
		    String useridfilename="twitterspamsendername.txt";
		    String uidfilename=this.instancesdirectory+useridfilename;   
		    File uidfile=new File(uidfilename);
		    if(!uidfile.exists())
		    {
		    	try {
		    		uidfile.createNewFile();
//				 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
	 
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		    }
		    else
		    {
		    	try {
			    	
						uidfile.delete();
						uidfile.createNewFile();
//					 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
		    		
		    	
	    		} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		    }
		    
		    FileWriter writer = null;
		    BufferedWriter bw=null;
	    	 
		    writer =new FileWriter(uidfilename, true);
	    	
		    bw=new BufferedWriter(writer);
		 
		 
		 
		 
		 Iterator it2=this.senderidhm.keySet().iterator();
		 while(it2.hasNext())
		 {
			 String sid=(String)it2.next();	
			 stmt.execute("SELECT id, name FROM user_info where id="+sid+" ;");
				
			 System.out.println("SELECT id,name FROM user_info where id="+sid+" ;");
			 System.out.println("start query "); 
			rs = stmt.getResultSet();
			System.out.println("done query "); 
			if(rs.next())
			{
				
//				rs.next();
				
//				rs.next();
				String name=rs.getString("name");
				if(name!=null)
				{
				 
				 bw.write(name);
				 bw.newLine();
	    		 bw.flush();
				}
				
			}
		
//				rs.close();
//				stmt.close();
		 }
		 bw.close();
    	 writer.close();
    	 bw=null;
    	 writer=null;
    	 
		 
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void distinctUserid(String filename,String useridfilename)
	{
		HashMap uidhm=new HashMap();
		/**
		 * read the file
		 */
		String idfilename=this.instancesdirectory+filename;
		File file=new File(idfilename);
		try {
			FileReader fr=new FileReader(file);
			BufferedReader br=new BufferedReader(fr);
			String line=null;
			while((line=br.readLine())!=null)
			{
				if(uidhm.containsKey(line.trim()))
				{
					String key=line.trim();
					Integer value=(Integer)uidhm.get(key);
					int intvalue=value.intValue()+1;
					value=Integer.valueOf(intvalue);
					uidhm.put(key, value);
				}
				else
				{
					String key=line.trim();
					Integer value=Integer.valueOf(1);
					uidhm.put(key, value);
				}
				
			}
			
			 String uidfilename=this.instancesdirectory+useridfilename;  
			    File uidfile=new File(uidfilename);
			    if(!uidfile.exists())
			    {
			    	try {
			    		uidfile.createNewFile();
//					 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
		 
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			    }
			    else
			    {
			    	try {
				    	
							uidfile.delete();
							uidfile.createNewFile();
//						 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
			    		
			    	
		    		} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			    }
			    
			/**
			 * write to the file
			 */
			FileWriter writer = null;
		    BufferedWriter bw=null;
	    	
		    writer =new FileWriter(uidfilename, true);
	    	
		    bw=new BufferedWriter(writer);
		    int totalnum=0;
		 
			Iterator it=uidhm.keySet().iterator();
			
			while(it.hasNext())
			{
				String key=(String) it.next();
				Integer value=(Integer) uidhm.get(key);
				String useridinfo=key+" "+value.intValue();
				 bw.write(useridinfo);
				 bw.newLine();
	    		 bw.flush();
	    		 totalnum++;
			}
			 bw.close();
	    	 writer.close();
	    	 bw=null;
	    	 writer=null;
	    	 System.out.println(totalnum);
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
	}
	
	public void distinctUserName(String filename,String useridfilename)
	{
		HashMap uidhm=new HashMap();
		/**
		 * read the file
		 */
		String idfilename=this.instancesdirectory+filename;
		File file=new File(idfilename);
		try {
			FileReader fr=new FileReader(file);
			BufferedReader br=new BufferedReader(fr);
			String line=null;
			while((line=br.readLine())!=null)
			{
				if(uidhm.containsKey(line.trim()))
				{
					String key=line.trim();
					Integer value=(Integer)uidhm.get(key);
					int intvalue=value.intValue()+1;
					value=Integer.valueOf(intvalue);
					uidhm.put(key, value);
				}
				else
				{
					String key=line.trim();
					Integer value=Integer.valueOf(1);
					uidhm.put(key, value);
				}
				
			}
			
			 String uidfilename=this.instancesdirectory+useridfilename;  
			    File uidfile=new File(uidfilename);
			    if(!uidfile.exists())
			    {
			    	try {
			    		uidfile.createNewFile();
//					 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
		 
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			    }
			    else
			    {
			    	try {
				    	
							uidfile.delete();
							uidfile.createNewFile();
//						 	this.save2arff(tempdata, this.instancesdirectory, spamorhamfilename);
			    		
			    	
		    		} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			    }
			    
			/**
			 * write to the file
			 */
			FileWriter writer = null;
		    BufferedWriter bw=null;
	    	
		    writer =new FileWriter(uidfilename, true);
	    	
		    bw=new BufferedWriter(writer);
		    int totalnum=0;
		 
			Iterator it=uidhm.keySet().iterator();
			
			while(it.hasNext())
			{
				String key=(String) it.next();
				Integer value=(Integer) uidhm.get(key);
				String useridinfo=key+" "+value.intValue();
				 bw.write(useridinfo);
				 bw.newLine();
	    		 bw.flush();
	    		 totalnum++;
			}
			 bw.close();
	    	 writer.close();
	    	 bw=null;
	    	 writer=null;
	    	 System.out.println(totalnum);
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
	}
	
	public static void main(String [] args)
	{
		TwitterMsgId2Userid tmiu=new TwitterMsgId2Userid();
		tmiu.init();
//		tmiu.getUserid();
		tmiu.getUserName();
//		tmiu.distinctUserid("twitterspamsenderid.txt", "distwitterspamsenderid.txt");
		
	}
}
